--    Program: 		CAPPOEXT_APPROVAL_GROUP.sql
--    Description:	Extract PO Approval Group information 
--    Revision:		1st     
--    Parameter: 	1. set of books short name e.g. SOB_C999

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;
alter session set nls_date_format='YYYYMMDD HH24:MI:SS';

declare   
   l_processed_count		   number;
   l_rejected_count 		   number;
   l_err_msg				   varchar2(2000);
                    		
   l_processed_count_r		   number;
   l_rejected_count_r 		   number;
   l_err_msg_r				   varchar2(2000);
  
   l_utl_file       		   utl_file.file_type;
   l_filename       		   varchar2(255);
   l_pathname       		   varchar2(100);
                    		
   l_utl_file_r       		   utl_file.file_type;
   l_filename_r       		   varchar2(255); 
   
   l_ngo_sob				   varchar2(20);
   l_ngo_sob_id     		   number;
                           
   l_set_of_books_id		   number;
  
   cursor cur_group (P_SET_OF_BOOKS_ID number) is
   select PCG.*
   from   PO_CONTROL_GROUPS_ALL PCG,
	      HR_ORGANIZATION_INFORMATION HOI
   where  HOI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
   and    HOI.ORG_INFORMATION3 = to_char(P_SET_OF_BOOKS_ID)
   and    HOI.ORGANIZATION_ID = PCG.ORG_ID;
		
   cursor cur_rule (P_CONTROL_GROUP_ID number) is
   select * 
   from   PO_CONTROL_RULES
   where  CONTROL_GROUP_ID = P_CONTROL_GROUP_ID; 

begin
   l_pathname := '/tmp';
   l_filename := 'CAPPO_APPROVAL_GROUP.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');   
  
   l_filename_r := 'CAPPO_APPROVAL_GROUP_RULE.dat';
   l_utl_file_r := utl_file.fopen(l_pathname, l_filename_r, 'w');

   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_processed_count_r := 0;
   l_rejected_count_r  := 0;  
   
   l_ngo_sob := '&1';
   
    begin
      select set_of_books_id
      into l_ngo_sob_id
      from gl_sets_of_books
      where upper(short_name) = upper(l_ngo_sob);
    exception
      when others then
        dbms_output.put_line(l_ngo_sob||': set of books name not found');
    end;

   for cur in cur_group(l_ngo_sob_id) loop   	
     
     -- output data
     utl_file.put_line( l_utl_file, 
     cur.CONTROL_GROUP_ID || chr(9) ||
     cur.CONTROL_GROUP_NAME || chr(9) ||
     cur.LAST_UPDATE_DATE|| chr(9) ||
     cur.LAST_UPDATED_BY|| chr(9) ||
     cur.LAST_UPDATE_LOGIN|| chr(9) ||
     cur.CREATION_DATE|| chr(9) ||
     cur.CREATED_BY || chr(9) ||
     cur.REQUEST_ID|| chr(9) ||
     cur.PROGRAM_APPLICATION_ID || chr(9) ||
     cur.PROGRAM_ID|| chr(9) ||
     cur.PROGRAM_UPDATE_DATE|| chr(9) ||
     cur.ENABLED_FLAG || chr(9) ||
     cur.DESCRIPTION|| chr(9) ||
     cur.ATTRIBUTE1|| chr(9) ||
     cur.ATTRIBUTE10|| chr(9) ||
     cur.ATTRIBUTE11|| chr(9) ||
     cur.ATTRIBUTE12|| chr(9) ||
     cur.ATTRIBUTE13|| chr(9) ||
     cur.ATTRIBUTE14|| chr(9) ||
     cur.ATTRIBUTE15 || chr(9) ||
     cur.ATTRIBUTE2|| chr(9) ||
     cur.ATTRIBUTE3|| chr(9) ||
     cur.ATTRIBUTE4|| chr(9) ||
     cur.ATTRIBUTE5|| chr(9) ||
     cur.ATTRIBUTE6 || chr(9) ||
     cur.ATTRIBUTE7 || chr(9) ||
     cur.ATTRIBUTE8|| chr(9) ||
     cur.ATTRIBUTE9|| chr(9) ||
     cur.ATTRIBUTE_CATEGORY || chr(9) ||
     cur.ORG_ID    
	 );
	    
	 l_processed_count := l_processed_count + 1;  
	    
	 for cur1 in cur_rule (cur.CONTROL_GROUP_ID) loop    
          
        utl_file.put_line( l_utl_file_r, 
          cur1.CONTROL_RULE_ID|| chr(9) ||
          cur1.CONTROL_GROUP_ID|| chr(9) ||
  		  cur1.LAST_UPDATE_DATE|| chr(9) ||
  		  cur1.LAST_UPDATED_BY|| chr(9) ||
  		  cur1.LAST_UPDATE_LOGIN|| chr(9) ||
  		  cur1.CREATION_DATE|| chr(9) ||
  		  cur1.CREATED_BY || chr(9) ||
		  cur1.PROGRAM_UPDATE_DATE || chr(9) ||
		  cur1.REQUEST_ID || chr(9) ||
 	  	  cur1.PROGRAM_APPLICATION_ID || chr(9) ||
 		  cur1.PROGRAM_ID|| chr(9) ||
 		  cur1.RULE_TYPE_CODE|| chr(9) ||
		  cur1.OBJECT_CODE|| chr(9) ||
  		  cur1.AMOUNT_LIMIT|| chr(9) ||
  		  cur1.LOCATION_ID|| chr(9) ||
  		  cur1.SEGMENT14_HIGH || chr(9) ||
  		  cur1.INACTIVE_DATE|| chr(9) ||
  		  cur1.SEGMENT12_LOW || chr(9) ||
  		  cur1.SEGMENT12_HIGH || chr(9) ||
  		  cur1.SEGMENT1_LOW|| chr(9) ||
  		  cur1.SEGMENT13_LOW|| chr(9) ||
  		  cur1.SEGMENT1_HIGH|| chr(9) ||
  		  cur1.SEGMENT13_HIGH|| chr(9) ||
  		  cur1.SEGMENT2_LOW|| chr(9) ||
  		  cur1.SEGMENT14_LOW|| chr(9) ||
  		  cur1.SEGMENT2_HIGH|| chr(9) ||
  		  cur1.SEGMENT3_LOW|| chr(9) ||
  		  cur1.SEGMENT15_LOW|| chr(9) ||
  		  cur1.SEGMENT3_HIGH|| chr(9) ||
  		  cur1.SEGMENT15_HIGH|| chr(9) ||
  		  cur1.SEGMENT4_LOW|| chr(9) ||
  		  cur1.SEGMENT16_LOW|| chr(9) ||
  		  cur1.SEGMENT4_HIGH|| chr(9) ||
  		  cur1.SEGMENT16_HIGH|| chr(9) ||
  		  cur1.SEGMENT5_LOW|| chr(9) ||
  		  cur1.SEGMENT17_LOW|| chr(9) ||
  		  cur1.SEGMENT5_HIGH|| chr(9) ||
  		  cur1.SEGMENT17_HIGH|| chr(9) ||
  		  cur1.SEGMENT6_LOW|| chr(9) ||
  		  cur1.SEGMENT18_LOW|| chr(9) ||
  		  cur1.SEGMENT6_HIGH|| chr(9) ||
  		  cur1.SEGMENT18_HIGH|| chr(9) ||
  		  cur1.SEGMENT7_LOW || chr(9) ||
  		  cur1.SEGMENT19_LOW|| chr(9) ||
  		  cur1.SEGMENT7_HIGH || chr(9) ||
  		  cur1.SEGMENT19_HIGH|| chr(9) ||
  		  cur1.SEGMENT8_LOW|| chr(9) ||
  		  cur1.SEGMENT20_LOW|| chr(9) ||
  		  cur1.SEGMENT8_HIGH|| chr(9) ||
  		  cur1.SEGMENT20_HIGH|| chr(9) ||
  		  cur1.SEGMENT9_LOW|| chr(9) ||
  		  cur1.SEGMENT21_LOW|| chr(9) ||
  		  cur1.SEGMENT9_HIGH|| chr(9) ||
  		  cur1.SEGMENT10_LOW|| chr(9) ||
  		  cur1.SEGMENT21_HIGH|| chr(9) ||
  		  cur1.SEGMENT10_HIGH|| chr(9) ||
  		  cur1.SEGMENT22_LOW|| chr(9) ||
  		  cur1.SEGMENT11_LOW|| chr(9) ||
  		  cur1.SEGMENT22_HIGH|| chr(9) ||
  		  cur1.SEGMENT11_HIGH|| chr(9) ||
  		  cur1.SEGMENT23_LOW|| chr(9) ||
  		  cur1.SEGMENT23_HIGH|| chr(9) ||
  		  cur1.SEGMENT24_LOW|| chr(9) ||
  		  cur1.SEGMENT24_HIGH|| chr(9) ||
  		  cur1.SEGMENT25_LOW|| chr(9) ||
  		  cur1.SEGMENT25_HIGH|| chr(9) ||
  		  cur1.SEGMENT26_LOW|| chr(9) ||
  		  cur1.SEGMENT26_HIGH|| chr(9) ||
  		  cur1.SEGMENT27_LOW|| chr(9) ||
  		  cur1.SEGMENT27_HIGH|| chr(9) ||
  		  cur1.SEGMENT28_LOW|| chr(9) ||
  		  cur1.SEGMENT28_HIGH || chr(9) ||
  		  cur1.SEGMENT29_LOW || chr(9) ||
  		  cur1.SEGMENT29_HIGH || chr(9) ||
  		  cur1.SEGMENT30_LOW || chr(9) ||
  		  cur1.SEGMENT30_HIGH || chr(9) ||
  		  cur1.ATTRIBUTE1 || chr(9) ||
  		  cur1.ATTRIBUTE2|| chr(9) ||
  		  cur1.ATTRIBUTE3|| chr(9) ||
  		  cur1.ATTRIBUTE4 || chr(9) ||
  		  cur1.ATTRIBUTE5 || chr(9) ||
  		  cur1.ATTRIBUTE6 || chr(9) ||
  		  cur1.ATTRIBUTE7 || chr(9) ||
  		  cur1.ATTRIBUTE8 || chr(9) ||
  		  cur1.ATTRIBUTE9 || chr(9) ||
  		  cur1.ATTRIBUTE10|| chr(9) ||
  		  cur1.ATTRIBUTE11|| chr(9) ||
  		  cur1.ATTRIBUTE12|| chr(9) ||
  		  cur1.ATTRIBUTE13 || chr(9) ||
  		  cur1.ATTRIBUTE14  || chr(9) ||
  		  cur1.ATTRIBUTE15  || chr(9) ||
  		  cur1.ATTRIBUTE_CATEGORY   
      );
      
       l_processed_count_r := l_processed_count_r + 1;  
    end loop;            
    
   end loop;

   if l_processed_count = 0 then   
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := l_ngo_sob || ': Apporval Group not found';
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ('CAPPOEXT_APPROVAL_GROUP', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   if l_processed_count_r = 0 then
     l_rejected_count_r := l_rejected_count_r + 1;
     l_err_msg_r := l_ngo_sob || ': Apporval Group Rule not found';
   end if;    
     
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
   values ( 'CAPPOEXT_APPROVAL_GROUP_RULE', sysdate, l_processed_count_r, l_rejected_count_r, l_processed_count_r-l_rejected_count_r, l_err_msg_r ); 
    
   commit;

   utl_file.fclose(l_utl_file);
   utl_file.fclose(l_utl_file_r);
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
